Re: Determining which index to create

--On mercredi 21 novembre 2001 22:48 +1100 Martijn van Oosterhout
<kleptog@svana.org> wrote:

> On Wed, Nov 21, 2001 at 12:23:07PM +0100, Eric Cholet wrote:
>> I would like to optimize the following query:
>>
>> => select * from dico_frs where motid=4742 order by date desc limit 10;
>>
>> But it's still quite slow. I'm thinking an index on (motid, date desc)
>> would be best
>> but that doesn't seem to be possible. How can I optimize this query?
>
> Indexes (at least btree ones) can be scanned in either forward or backward
> directions. So an index on (motid,date) should be fine.

I should have mentionned I tried that, but it isn't being used:

=> \d dico_frs_motid_date
     Index "dico_frs_motid_date"
 Attribute |           Type
-----------+--------------------------
 motid     | integer
 date      | timestamp with time zone
btree

=> explain select * from dico_frs where motid=4742 order by date desc limit
10;
NOTICE:  QUERY PLAN:

Limit  (cost=0.00..17591.91 rows=10 width=16)
  ->  Index Scan Backward using dico_frs_date on dico_frs
(cost=0.00..20023641.63 rows=11382 width=16)

--
Eric Cholet